-- @owner: ningyali
-- @date: 2024-08-12
-- @testpoint: count(column) column列为自增列,查询其他列中索引-执行计划有优化

--step1:创建测试表; expect: 执行成功
drop table if exists t_ustore_count_optim_0010;
create table t_ustore_count_optim_0010(
	c_id int  primary key, c_int  integer, c_bool bool,
	c_real real,
	c_char char(300),c_varchar2 varchar2(4000) default lpad('ee2t2xnbzzkih',80,'etskbfw@p%my8tesjbemus6gnv3'),
	c_timestamp timestamp not null
	) with (storage_type=ustore);

--step2:创建序列; expect: 执行成功
drop sequence if exists seq_ustore_count_optim_0010;
create sequence seq_ustore_count_optim_0010 increment by 1 start with 100000;

--step3:插入数据; expect: 执行成功
begin
	for i in 1..1000 loop
	insert into t_ustore_count_optim_0010(c_id,c_int,c_bool,c_real,c_char,c_timestamp) values(seq_ustore_count_optim_0010.nextval,i+3,'true',i%16+4.9690546872 ,lpad('ee2t2xnbzzkih',50,'et'),to_timestamp('19-aug-2004 21:38:09.123000','dd-mon-yyyy hh24:mi:ss.ff') );
    end loop;
end;
/

--step4: count(column) column列为自增列,查询其他列中索引-执行计划有优化; expect: 执行成功
explain analyze select c_char, count(c_id) from t_ustore_count_optim_0010
group by c_char order by 1 limit 5;

select c_int,count(c_id) over(order by c_varchar2) from t_ustore_count_optim_0010
where c_int < 200 limit 5;

explain analyze select c_int,count(c_id) over(order by c_varchar2)
from t_ustore_count_optim_0010 where c_int < 200 limit 5;

explain analyze select c_int,count(*) over(order by c_varchar2)
from t_ustore_count_optim_0010 where c_int < 200 limit 5;

--step5:清理环境; expect: 执行成功
drop table if exists t_ustore_count_optim_0010;
drop sequence if exists seq_ustore_count_optim_0010;